package dao;

import entity.User;
import tool.JNDIUtils;

import java.sql.*;
import java.util.Scanner;

/**
 * @author shijigao
 * @create 2021-06-06 17:45
 * @purpose:
 */

public class MessageDAO {
    /**
     * @param sender：发消息的人
     * @param receiver：收消息的人
     * @return：是否成功接收消息,成功返回true，失败返回false
     * 注意：此时因为主键为group_id，所以每个组只能发一条消息(可优化，等后续方案)
     */
    //sender向receiver发送消息,返回是否发送成功
    public static boolean sendMessage(User sender, User receiver) {
        Scanner in = new Scanner(System.in);
        Connection conn = null;
        PreparedStatement ps = null;
        boolean bool = false;
        try {
            String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
            String user = "root";
            String password = "123456";
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            //预编译sql语句
            String sql = "INSERT INTO `Group_Message`(`message`,`group_id`,`sender`,`receiver`,`submit_time`)values(?,?,?,?,current_timestamp); ";
            ps = conn.prepareStatement(sql);

            //要发送的语句
            System.out.println("请输入你要发送的语句:");
            String message = in.nextLine();

            //获取发送者的小组id
            int group_id = getGroup_id(sender);
            /*
            这里要对小组id是否存在进行判断，如果该id在表中不存在的话，会报
            java.sql.SQLIntegrityConstraintViolationException异常
            即外键约束失败
             */
            if (group_id==-1){
                System.out.println("小组id不存在");
                return false;
            }
            //获取发送者和接收者的id
            int sender_id = sender.getId();
            int receiver_id = receiver.getId();

            ps.setString(1, message);
            ps.setInt(2, group_id);
            ps.setInt(3, sender_id);
            ps.setInt(4, receiver_id);
            ps.execute();
            bool = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.release(ps, conn);
            return bool;
        }
    }

    //返回接收者收到的消息,这里的参数(user)为接收者
    public static String MessageReceive(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String message = null;
        try {
            String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
            String username = "root";
            String password = "123456";
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            String sql = "select `message` from `Group_Message` where receiver = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            rs = ps.executeQuery();
            while (rs.next()) {
                message = rs.getString(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.release(rs, ps, conn);
            return message;
        }
    }

    //根据用户查找其所在小组id,返回-1代表没有小组
    public static int getGroup_id(User user){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int group_id=-1;
        try {
            String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
            String username = "root";
            String password = "123456";
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            String sql = "SELECT `group_id` FROM `Group_Relation` WHERE ? IN (`member_id1`,`member_id2`,`member_id3`,`member_id4`,`member_id5`,`member_id6`,`member_id7`,`member_id8`,`member_id9`,`member_id10`);";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            rs = ps.executeQuery();
            while (rs.next()) {
                group_id=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JNDIUtils.release(rs, ps, conn);
            return group_id;
        }
    }

    //删除该用户（user）所收到的所有消息,返回是否删除成功
    public static boolean deleteMessage(User user){
        boolean bool=false;
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
        String username="root";
        String password="123456";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            String sql="DELETE `Group_Message` FROM `Group_Message` WHERE `receiver`= ?;";
            ps = conn.prepareStatement(sql);
            //根据用户获得id
            ps.setInt(1,user.getId());
            ps.execute();
            bool=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JNDIUtils.release(ps,conn);
            return bool;
        }
    }

    //删除该小组的所有消息,返回是否删除成功
    public static boolean deleteMessage(int groupId){
        boolean bool=false;
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
        String username="root";
        String password="123456";
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            String sql="DELETE `Group_Message` FROM `Group_Message` WHERE `group_id` = ?;";
            ps = conn.prepareStatement(sql);
            //根据用户获得id
            ps.setInt(1,groupId);
            ps.execute();
            bool=true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JNDIUtils.release(ps,conn);
            return bool;
        }
    }

    //查询`Group_Message`表中是否已经有user所在小组id的消息，如果有的话返回true
    public static boolean groupMessageExist(User user){
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&allowPublicKeyRetrieval=true";
        String username="root";
        String password="123456";
        int count=0;
        boolean bool=false;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
            String sql="SELECT COUNT(*) FROM `Group_Message` WHERE `group_id`= ? ;";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,MessageDAO.getGroup_id(user));
            rs = ps.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
        JNDIUtils.release(rs,ps,conn);
        if (count>0){//count>0时代表该小组id出现了
            bool=true;
        }
        return bool;
        }
    }
}
